package com.core.utils;

import java.util.ArrayList;
import java.util.List;

import com.core.entity.Condition;
import com.core.entity.MultiFieldCondition;
import com.core.entity.Query;
import com.core.entity.SingleFieldCondition;
import com.core.plugin.sqlinxml.SqlManager;
import com.google.common.base.Joiner;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Table;
import com.jfinal.plugin.activerecord.TableMapping;
@SuppressWarnings("rawtypes")
public class SqlKit {
	
	public static final String BLANK = " ";
	public static final String AND = " AND ";
	public static final String OR = " OR ";
	public static final String IN = "in";
	public static final String SELECT = "SELECT ";
	public static final String FROM = " FROM ";
	public static final String WHERE = " WHERE ";
	
    /**
     * 获取SQL 复杂的sql写在 xml中
     */
    public static String sql(String key) {
    	String sql = SqlManager.sql(key);
    	if(StrKit.isBlank(sql)){
    		System.err.println("sql语句不存在：sql id是" + key);
    	}
        return sql;
    }
    
    /**
     * 动态拼接sql
     */
	public static String sql(List<Condition> conditions,Class<? extends Model> tableClass,String[] sqlSelect){
    	String tableName = getTable(tableClass).getName();
    	QueryBuilder qb = dynamicCondition(conditions);
    	StringBuilder findSql = new StringBuilder();
    	findSql.append(SELECT).append(Joiner.on(",").join(sqlSelect)).append(FROM).append(tableName);
    	if(StrKit.notBlank(qb.toString()))
    		findSql.append(WHERE).append(qb.toString());
		return findSql.toString();
    }
	
	/**
	 * 动态拼接sql
	 */
	public static String sql(Condition condition,Class<? extends Model> tableClass,String[] sqlSelect){
		String tableName = getTable(tableClass).getName();
		QueryBuilder qb = dynamicCondition(condition);
		StringBuilder findSql = new StringBuilder();
		findSql.append(SELECT).append(Joiner.on(",").join(sqlSelect)).append(FROM).append(tableName);
		if(StrKit.notBlank(qb.toString()))
			findSql.append(WHERE).append(qb.toString());
		return findSql.toString();
	}
	
	/**
     * 动态拼接sql
     */
	public static String sql(List<Condition> conditions,Class<? extends Model> tableClass){
		return sql(conditions, tableClass, new String[]{"*"});
	}
	
	/**
	 * 动态拼接sql
	 */
	public static String sql(Condition condition,Class<? extends Model> tableClass){
		return sql(condition, tableClass, new String[]{"*"});
	}
	
	/**
	 * 动态拼接sql
	 */
	public static String sql(Class<? extends Model> tableClass, String[] sqlSelect){
		String tableName = getTable(tableClass).getName();
		StringBuilder findSql = new StringBuilder();
    	findSql.append(SELECT).append(Joiner.on(",").join(sqlSelect)).append(FROM).append(tableName);
		return findSql.toString();
	}
	
	/**
	 * 动态拼接sql
	 */
	public static String sql(Class<? extends Model> tableClass){
		return sql(tableClass, new String[]{"*"});
	}
	
	/**
	 * 构建Query对象
	 */
	public static Query buildQuery(String tableName , List<Condition> conditions){
		StringBuilder findSql = new StringBuilder();
		if(conditions.size() > 0){
			findSql.append(FROM + tableName + WHERE +"1 = 1 ");
		}else{
			findSql.append(FROM + tableName + BLANK);
		}
		Query query = SqlKit.dynamicConditionsNoWhere(conditions);
		query.setSql(findSql.append(query.getSql()).toString());
		System.err.println(query.getSql());
		for (Object param : query.getParamList()) {
			System.err.print(param+"-");
		}
		return query;
	}
	
	public static QueryBuilder dynamicCondition(List<Condition> conditions){
		QueryBuilder queryBuilder = new QueryBuilder();
		for (Condition condition : conditions) {
			QueryBuilder qb = dynamicCondition(condition);
			if(StrKit.notBlank(qb.toString()))
				queryBuilder.and(qb);
		}
		return queryBuilder;
	}
	
	public static QueryBuilder dynamicCondition(Condition condition){
		QueryBuilder queryBuilder = new QueryBuilder();
		if(StrKit.notNull(condition.getNewValue())){
			if (condition instanceof SingleFieldCondition) {
				queryBuilder.and(condition.getField(), condition.getNewValue(), condition.getOperator());
			}else{
				List<SingleFieldCondition> singleFieldConditions = ((MultiFieldCondition) condition).getSingleConditions();
				for (SingleFieldCondition singleFieldCondition : singleFieldConditions) {
					queryBuilder.or(singleFieldCondition.getField(), singleFieldCondition.getNewValue(), singleFieldCondition.getOperator());
				}
			}
		}
		return queryBuilder;
	}
	
	public static Query dynamicConditionsNoWhere(List<Condition> conditions){
		StringBuilder sb = new StringBuilder();
		List<Object> paramList = new ArrayList<Object>();
		for (Condition condition : conditions) {
			Object newValue = condition.getNewValue();
			if(StrKit.notNull(newValue) && StrKit.notBlank(newValue.toString())){
				if (condition instanceof SingleFieldCondition) {
					Query query = andCondition(condition) ;
					sb.append(AND).append(query.getSql());
					paramList.addAll(query.getParamList());
				} else if(condition instanceof MultiFieldCondition){
					sb.append(AND).append("(");
					List<SingleFieldCondition> singleFieldConditions = ((MultiFieldCondition) condition).getSingleConditions();
					for (int i = 0; i < singleFieldConditions.size(); i++) {
						if(i>0)
							sb.append(OR);
						Query query = orCondition(singleFieldConditions.get(i));
						sb.append(query.getSql());
						paramList.addAll(query.getParamList());
					}
					sb.append(")");
				}
			}
		}
		Query query = new Query();
		query.setSql(sb.toString());
		query.setParamList(paramList);
		return query;
	}
	
	public static Query andCondition(Condition condition){
		Query query = new Query();
		String op = condition.getOperator();
		if(StrKit.isBlank(op)){
			return query;
		}
		Object newValue = condition.getNewValue();
		String field = condition.getField();
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append(field).append(BLANK).append(op).append(BLANK);
		if(op.equals(IN)){
			String[] values = (String[]) newValue;
			sb.append("(");
			for (int i = 0; i < values.length; i++) {
				sb.append("?");
				paramList.add(values[i]);
				if(i < values.length-1){
					sb.append(",");
				}else{
					sb.append(")");
				}
			}
		}else{
			sb.append("?");
			paramList.add(newValue);
		}
		query.setSql(sb.toString());
		query.setParamList(paramList);
		return query;
	}
	
	public static Query orCondition(SingleFieldCondition condition){
		return andCondition(condition);
	}
	
	/**
	 * 获取表映射对象
	 */
	public static Table getTable(Class<? extends Model> tableClass) {
		return TableMapping.me().getTable(tableClass);
	}

}
